import os
import numpy as np
import pandas as pd


def write_csv(statics, path):
    data = []
    for i in range(len(statics)):
        data.append([str(i + 1) + '\t' + statics[i][0] + '\t' + statics[i][1] + '\t' + str(statics[i][2])])
    df = pd.DataFrame(data)
    df.to_csv(path, header=False, index=False, mode='w')


def read_xlsx(path):
    df = pd.read_excel(path)
    xlsx = []
    for i in range(len(df)):
        sen1 = df.iloc[i, 1].split(',')
        sen1 = " ".join(sen1)
        sen2 = df.iloc[i, 2].split(',')
        sen2 = " ".join(sen2)
        if sen1.find(':') > 0 or sen2.find(':') > 0:
            continue
        if sen1.find('\"') > 0 or sen2.find('\"') > 0:
            continue
        sen1_length = len(sen1.split())
        sen2_length = len(sen2.split())
        if sen1_length > 512 or sen2_length > 512:
            continue
        score = float(df.iloc[i, 3][2: -2])
        score = str(score)
        data = [sen1, sen2, score]
        xlsx.append(data)
    return xlsx


def read_xlsx_length(path):
    df = pd.read_excel(path)
    xlsx_short = []
    xlsx_long = []
    score_list = []
    for i in range(len(df)):
        score_list.append(float(df.iloc[i, 3][2: -2]))
    score_list = (score_list - np.mean(score_list)) / np.std(score_list)  # Z-score标准化
    for i in range(len(df)):
        sen1 = df.iloc[i, 1].split(',')
        sen1 = " ".join(sen1)
        sen1 = " ".join(sen1.split('\n'))
        sen2 = df.iloc[i, 2].split(',')
        sen2 = " ".join(sen2)
        sen2 = " ".join(sen2.split('\n'))
        if sen1.find(':') > 0 or sen2.find(':') > 0:
            continue
        if sen1.find('\"') > 0 or sen2.find('\"') > 0:
            continue
        sen1_length = len(sen1.split())
        sen2_length = len(sen2.split())
        if sen1_length > 512 or sen2_length > 512:
            continue
        score = 1.0 / (1 + np.exp(-score_list[i]))  # sigmoid
        # score = float(df.iloc[i, 3][2: -2])
        score = str(score)
        data = [sen1, sen2, score]
        if sen1_length > 256 or sen2_length > 256:
            xlsx_long.append(data)
        else:
            xlsx_short.append(data)
    return xlsx_short, xlsx_long


if __name__ == '__main__':
    sheet_short, sheet_long = read_xlsx_length('./data/outcome1.xlsx')
    write_csv(sheet_short, './data/outcome1_short.csv')
    write_csv(sheet_long, './data/outcome1_long.csv')

    # sheet = read_xlsx('./data/outcome1.xlsx')
    # write_csv(sheet, './data/outcome1.csv')
